using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public MvtofinVO getMvtofin(int? idMvtoFin)
		{
			MvtofinVO mvtofinVO = null;
			List<MvtofinVO> listMvtofinVOs = getMvtofins(idMvtoFin, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
			if (listMvtofinVOs != null && listMvtofinVOs.Count > 0)
			{
				mvtofinVO = listMvtofinVOs[0];
			}
			return mvtofinVO;
		}

		public List<MvtofinVO> getMvtofins()
		{
			return getMvtofins(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
		}

		public List<MvtofinVO> getMvtofins(int? idMvtoFin)
		{
			return getMvtofins(idMvtoFin, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
		}

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, dtMvtoFinDe, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe, DateTime? dtMvtoFinAte)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, dtMvtoFinDe, dtMvtoFinAte, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe, DateTime? dtMvtoFinAte, decimal? valorDe)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, dtMvtoFinDe, dtMvtoFinAte, valorDe, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe, DateTime? dtMvtoFinAte, decimal? valorDe, decimal? valorAte)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, dtMvtoFinDe, dtMvtoFinAte, valorDe, valorAte, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe, DateTime? dtMvtoFinAte, decimal? valorDe, decimal? valorAte, string nrDoc)
        {
            return getMvtofins(idMvtoFin, idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, natureza, tipo, idBanco, dtMvtoFinDe, dtMvtoFinAte, valorDe, valorAte, nrDoc, null);
        }

        public List<MvtofinVO> getMvtofins(int? idMvtoFin, int? idFilial, int? idCtaCor, int? idFornecedor, int? idCliente, int? idFormaPgto, int? idPlnCta, int? idCLucro, int? idCCusto, int? idCaixa, int? idTitRec, int? idTitPag, char? natureza, char? tipo, int? idBanco, DateTime? dtMvtoFinDe, DateTime? dtMvtoFinAte, decimal? valorDe, decimal? valorAte, string nrDoc, char? conciliacao)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT mvf.idMvtoFin as mvf_idMvtoFin, mvf.idFilial as mvf_idFilial, fil.filial as fil_filial, mvf.idCtaCor as mvf_idCtaCor, ctc.ctaCor as ctc_ctaCor, mvf.idFornecedor as mvf_idFornecedor, mvf.idCliente as mvf_idCliente, mvf.idFormaPgto as mvf_idFormaPgto, fpg.formaPgto as fpg_formaPgto, mvf.idPlnCta as mvf_idPlnCta, plc.plnCta as plc_plnCta, mvf.idCLucro as mvf_idCLucro, clc.cLucro as clc_cLucro, mvf.idCCusto as mvf_idCCusto, cct.cCusto as cct_cCusto, mvf.idCaixa as mvf_idCaixa, cxa.caixa as cxa_caixa, mvf.idTitRec as mvf_idTitRec, mvf.idTitPag as mvf_idTitPag, ttp.nrDoc as ttp_nrDoc, mvf.nrDoc as mvf_nrDoc, mvf.dtMvtoFin as mvf_dtMvtoFin, mvf.valor as mvf_valor, mvf.valorJuro as mvf_valorJuro, mvf.valorDesc as mvf_valorDesc, mvf.valorPago as mvf_valorPago, mvf.natureza as mvf_natureza, mvf.conciliacao as mvf_conciliacao, mvf.cadastro as mvf_cadastro, mvf.cadastradoPor as mvf_cadastradoPor, mvf.edicao as mvf_edicao, mvf.editadoPor as mvf_editadoPor  ");
            query.AppendLine("FROM mvtofin as mvf ");
            query.AppendLine("  LEFT OUTER JOIN filial as fil on mvf.idFilial=fil.idFilial ");
            query.AppendLine("      LEFT OUTER JOIN ctacor as ctc on mvf.idCtaCor=ctc.idCtaCor");
            query.AppendLine("          LEFT OUTER JOIN banco as ban on ctc.idBanco=ban.idBanco");
            query.AppendLine("              LEFT OUTER JOIN fornecedor as frn on mvf.idFornecedor=frn.idFornecedor ");
            query.AppendLine("                  LEFT OUTER JOIN cliente as cli on mvf.idCliente=cli.idCliente ");
            query.AppendLine("                      LEFT OUTER JOIN formapgto as fpg on mvf.idFormaPgto=fpg.idFormaPgto ");
            query.AppendLine("                          LEFT OUTER JOIN plncta as plc on mvf.idPlnCta=plc.idPlnCta ");
            query.AppendLine("                              LEFT OUTER JOIN clucro as clc on mvf.idCLucro=clc.idCLucro ");
            query.AppendLine("                                  LEFT OUTER JOIN ccusto as cct on mvf.idCCusto=cct.idCCusto ");
            query.AppendLine("                                      LEFT OUTER JOIN caixa as cxa on mvf.idCaixa=cxa.idCaixa ");
            query.AppendLine("                                          LEFT OUTER JOIN titrec as ttr on mvf.idTitRec=ttr.idTitRec ");
            query.AppendLine("                                              LEFT OUTER JOIN titpag as ttp on mvf.idTitPag=ttp.idTitPag  ");
			if (idMvtoFin != null)
			{
				query.AppendLine(where + "mvf.idMvtoFin=@idMvtoFin ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "mvf.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idCtaCor != null)
			{
				query.AppendLine(where + "mvf.idCtaCor=@idCtaCor ");
				where = "AND ";
			}
			if (idFornecedor != null)
			{
				query.AppendLine(where + "mvf.idFornecedor=@idFornecedor ");
				where = "AND ";
			}
			if (idCliente != null)
			{
				query.AppendLine(where + "mvf.idCliente=@idCliente ");
				where = "AND ";
			}
			if (idFormaPgto != null)
			{
				query.AppendLine(where + "mvf.idFormaPgto=@idFormaPgto ");
				where = "AND ";
			}
			if (idPlnCta != null)
			{
				query.AppendLine(where + "mvf.idPlnCta=@idPlnCta ");
				where = "AND ";
			}
			if (idCLucro != null)
			{
				query.AppendLine(where + "mvf.idCLucro=@idCLucro ");
				where = "AND ";
			}
			if (idCCusto != null)
			{
				query.AppendLine(where + "mvf.idCCusto=@idCCusto ");
				where = "AND ";
			}
			if (idCaixa != null)
			{
				query.AppendLine(where + "mvf.idCaixa=@idCaixa ");
				where = "AND ";
			}
			if (idTitRec != null)
			{
				query.AppendLine(where + "mvf.idTitRec=@idTitRec ");
				where = "AND ";
			}
			if (idTitPag != null)
			{
				query.AppendLine(where + "mvf.idTitPag=@idTitPag ");
				where = "AND ";
			}
			if (natureza != null)
			{
				query.AppendLine(where + "mvf.natureza=@natureza ");
				where = "AND ";
			}
            if (tipo != null)
            {
                if (tipo == 'C')
                    query.AppendLine(where + "mvf.idCaixa is not null");
                if (tipo == 'R')
                    query.AppendLine(where + "mvf.idTitRec is not null");
                if (tipo == 'P')
                    query.AppendLine(where + "mvf.idTitPag is not null");
                where = "AND ";
            }
            if (idBanco != null)
            {
                query.AppendLine(where + "ctc.idBanco=@idBanco ");
                where = "AND ";
            }
            if (dtMvtoFinDe != null)
            {
                query.AppendLine(where + "mvf.dtMvtoFin>=@dtMvtoFinDe ");
                where = "AND ";
            }
            if (dtMvtoFinAte != null)
            {
                query.AppendLine(where + "mvf.dtMvtoFin<=@dtMvtoFinAte ");
                where = "AND ";
            }
            if (valorDe != null)
            {
                query.AppendLine(where + "mvf.valor>=@valorDe ");
                where = "AND ";
            }
            if (valorAte != null)
            {
                query.AppendLine(where + "mvf.valor<=@valorAte ");
                where = "AND ";
            }
            if (nrDoc != null)
            {
                query.AppendLine(where + "mvf.nrDoc LIKE @nrDoc ");
                where = "AND ";
            }
            if (conciliacao != null)
            {
                query.AppendLine(where + "mvf.conciliacao=@conciliacao ");
            }


            query.AppendLine("ORDER BY mvf.dtMvtoFin DESC");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idMvtoFin!= null) cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", idMvtoFin));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idCtaCor!= null) cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", idCtaCor));
			if (idFornecedor!= null) cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", idFornecedor));
			if (idCliente!= null) cmd.Parameters.Add(getParameter(cmd, "@idCliente", idCliente));
			if (idFormaPgto!= null) cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", idFormaPgto));
			if (idPlnCta!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", idPlnCta));
			if (idCLucro!= null) cmd.Parameters.Add(getParameter(cmd, "@idCLucro", idCLucro));
			if (idCCusto!= null) cmd.Parameters.Add(getParameter(cmd, "@idCCusto", idCCusto));
			if (idCaixa!= null) cmd.Parameters.Add(getParameter(cmd, "@idCaixa", idCaixa));
			if (idTitRec!= null) cmd.Parameters.Add(getParameter(cmd, "@idTitRec", idTitRec));
			if (idTitPag!= null) cmd.Parameters.Add(getParameter(cmd, "@idTitPag", idTitPag));
			if (natureza!= null) cmd.Parameters.Add(getParameter(cmd, "@natureza", natureza));
            if (idBanco != null) cmd.Parameters.Add(getParameter(cmd, "@idBanco", idBanco));
            if (dtMvtoFinDe != null) cmd.Parameters.Add(getParameter(cmd, "@dtMvtoFinDe", dtMvtoFinDe));
            if (dtMvtoFinAte != null) cmd.Parameters.Add(getParameter(cmd, "@dtMvtoFinAte", dtMvtoFinAte));
            if (valorDe != null) cmd.Parameters.Add(getParameter(cmd, "@valorDe", valorDe));
            if (valorAte != null) cmd.Parameters.Add(getParameter(cmd, "@valorAte", valorAte));
            if (nrDoc != null) cmd.Parameters.Add(getParameter(cmd, "@nrDoc", "%" + nrDoc + "%"));
            if (conciliacao != null) cmd.Parameters.Add(getParameter(cmd, "@conciliacao", conciliacao));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<MvtofinVO> listMvtofin = new List<MvtofinVO>();

				while (reader.Read())
				{
					MvtofinVO mvtofinVO = MvtofinVO.CreateVO();
					mvtofinVO.idMvtoFin = reader.GetInt32(reader.GetOrdinal("mvf_idMvtoFin"));
					mvtofinVO.idFilial = reader.GetInt32(reader.GetOrdinal("mvf_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						mvtofinVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idCtaCor")))
						mvtofinVO.idCtaCor = reader.GetInt32(reader.GetOrdinal("mvf_idCtaCor"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctc_ctaCor")))
						mvtofinVO.idCtaCorVO.ctaCor = reader.GetString(reader.GetOrdinal("ctc_ctaCor"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idFornecedor")))
						mvtofinVO.idFornecedor = reader.GetInt32(reader.GetOrdinal("mvf_idFornecedor"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idCliente")))
						mvtofinVO.idCliente = reader.GetInt32(reader.GetOrdinal("mvf_idCliente"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idFormaPgto")))
						mvtofinVO.idFormaPgto = reader.GetInt32(reader.GetOrdinal("mvf_idFormaPgto"));
					if (!reader.IsDBNull(reader.GetOrdinal("fpg_formaPgto")))
						mvtofinVO.idFormaPgtoVO.formaPgto = reader.GetString(reader.GetOrdinal("fpg_formaPgto"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idPlnCta")))
						mvtofinVO.idPlnCta = reader.GetInt32(reader.GetOrdinal("mvf_idPlnCta"));
					if (!reader.IsDBNull(reader.GetOrdinal("plc_plnCta")))
						mvtofinVO.idPlnCtaVO.plnCta = reader.GetString(reader.GetOrdinal("plc_plnCta"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idCLucro")))
						mvtofinVO.idCLucro = reader.GetInt32(reader.GetOrdinal("mvf_idCLucro"));
					if (!reader.IsDBNull(reader.GetOrdinal("clc_cLucro")))
						mvtofinVO.idCLucroVO.cLucro = reader.GetString(reader.GetOrdinal("clc_cLucro"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idCCusto")))
						mvtofinVO.idCCusto = reader.GetInt32(reader.GetOrdinal("mvf_idCCusto"));
					if (!reader.IsDBNull(reader.GetOrdinal("cct_cCusto")))
						mvtofinVO.idCCustoVO.cCusto = reader.GetString(reader.GetOrdinal("cct_cCusto"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idCaixa")))
						mvtofinVO.idCaixa = reader.GetInt32(reader.GetOrdinal("mvf_idCaixa"));
					if (!reader.IsDBNull(reader.GetOrdinal("cxa_caixa")))
						mvtofinVO.idCaixaVO.caixa = reader.GetString(reader.GetOrdinal("cxa_caixa"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idTitRec")))
						mvtofinVO.idTitRec = reader.GetInt32(reader.GetOrdinal("mvf_idTitRec"));
                    if (!reader.IsDBNull(reader.GetOrdinal("ttp_nrDoc")))
                        mvtofinVO.idTitPagVO.nrDoc = reader.GetString(reader.GetOrdinal("ttp_nrDoc"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_idTitPag")))
						mvtofinVO.idTitPag = reader.GetInt32(reader.GetOrdinal("mvf_idTitPag"));
                    if (!reader.IsDBNull(reader.GetOrdinal("mvf_nrDoc")))
                        mvtofinVO.nrDoc = reader.GetString(reader.GetOrdinal("mvf_nrDoc"));
					mvtofinVO.dtMvtoFin = reader.GetDateTime(reader.GetOrdinal("mvf_dtMvtoFin"));
					mvtofinVO.valor = reader.GetDecimal(reader.GetOrdinal("mvf_valor"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_valorJuro")))
						mvtofinVO.valorJuro = reader.GetDecimal(reader.GetOrdinal("mvf_valorJuro"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_valorDesc")))
						mvtofinVO.valorDesc = reader.GetDecimal(reader.GetOrdinal("mvf_valorDesc"));
					if (!reader.IsDBNull(reader.GetOrdinal("mvf_valorPago")))
						mvtofinVO.valorPago = reader.GetDecimal(reader.GetOrdinal("mvf_valorPago"));
					mvtofinVO.natureza = reader.GetChar(reader.GetOrdinal("mvf_natureza"));
                    if (!reader.IsDBNull(reader.GetOrdinal("mvf_conciliacao")))
                        mvtofinVO.conciliacao = reader.GetChar(reader.GetOrdinal("mvf_conciliacao"));
					mvtofinVO.cadastro = reader.GetDateTime(reader.GetOrdinal("mvf_cadastro"));
					mvtofinVO.cadastradoPor = reader.GetString(reader.GetOrdinal("mvf_cadastradoPor"));
					mvtofinVO.edicao = reader.GetDateTime(reader.GetOrdinal("mvf_edicao"));
					mvtofinVO.editadoPor = reader.GetString(reader.GetOrdinal("mvf_editadoPor"));


					listMvtofin.Add(mvtofinVO);
				}
				reader.Close();
				return listMvtofin;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setMvtofin(MvtofinVO mvtofinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE mvtofin ");
            query.AppendLine("SET idFilial=@idFilial, idCtaCor=@idCtaCor, idFornecedor=@idFornecedor, idCliente=@idCliente, idFormaPgto=@idFormaPgto, idPlnCta=@idPlnCta, idCLucro=@idCLucro, idCCusto=@idCCusto, idCaixa=@idCaixa, idTitRec=@idTitRec, idTitPag=@idTitPag, nrDoc=@nrDoc, dtMvtoFin=@dtMvtoFin, valor=@valor, valorJuro=@valorJuro, valorDesc=@valorDesc, valorPago=@valorPago, natureza=@natureza, conciliacao=@conciliacao, cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idMvtoFin=@idMvtoFin ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", mvtofinVO.idMvtoFin));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", mvtofinVO.idFilial));
			if (mvtofinVO.idCtaCor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", mvtofinVO.idCtaCor));
			if (mvtofinVO.idFornecedor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", mvtofinVO.idFornecedor));
			if (mvtofinVO.idCliente == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", mvtofinVO.idCliente));
			if (mvtofinVO.idFormaPgto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", mvtofinVO.idFormaPgto));
			if (mvtofinVO.idPlnCta == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", mvtofinVO.idPlnCta));
			if (mvtofinVO.idCLucro == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCLucro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCLucro", mvtofinVO.idCLucro));
			if (mvtofinVO.idCCusto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", mvtofinVO.idCCusto));
			if (mvtofinVO.idCaixa == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixa", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixa", mvtofinVO.idCaixa));
			if (mvtofinVO.idTitRec == null)
				cmd.Parameters.Add(getParameter(cmd, "@idTitRec", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idTitRec", mvtofinVO.idTitRec));
			if (mvtofinVO.idTitPag == null)
				cmd.Parameters.Add(getParameter(cmd, "@idTitPag", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idTitPag", mvtofinVO.idTitPag));
            if (mvtofinVO.nrDoc == null)
                cmd.Parameters.Add(getParameter(cmd, "@nrDoc", System.DBNull.Value));
            else
                cmd.Parameters.Add(getParameter(cmd, "@nrDoc", mvtofinVO.nrDoc));
			cmd.Parameters.Add(getParameter(cmd, "@dtMvtoFin", mvtofinVO.dtMvtoFin));
			cmd.Parameters.Add(getParameter(cmd, "@valor", mvtofinVO.valor));
			if (mvtofinVO.valorJuro == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorJuro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorJuro", mvtofinVO.valorJuro));
			if (mvtofinVO.valorDesc == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorDesc", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorDesc", mvtofinVO.valorDesc));
			if (mvtofinVO.valorPago == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorPago", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorPago", mvtofinVO.valorPago));
            cmd.Parameters.Add(getParameter(cmd, "@natureza", mvtofinVO.natureza));
            cmd.Parameters.Add(getParameter(cmd, "@conciliacao", mvtofinVO.conciliacao));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", mvtofinVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", mvtofinVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", mvtofinVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", mvtofinVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newMvtofin(MvtofinVO mvtofinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO mvtofin ");
            query.AppendLine("  ( idFilial, idCtaCor, idFornecedor, idCliente, idFormaPgto, idPlnCta, idCLucro, idCCusto, idCaixa, idTitRec, idTitPag, nrDoc, dtMvtoFin, valor, valorJuro, valorDesc, valorPago, natureza, conciliacao, cadastro, cadastradoPor, edicao, editadoPor ) ");
            query.AppendLine("VALUES ( @idFilial, @idCtaCor, @idFornecedor, @idCliente, @idFormaPgto, @idPlnCta, @idCLucro, @idCCusto, @idCaixa, @idTitRec, @idTitPag, @nrDoc, @dtMvtoFin, @valor, @valorJuro, @valorDesc, @valorPago, @natureza, @conciliacao, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", mvtofinVO.idFilial));
			if (mvtofinVO.idCtaCor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", mvtofinVO.idCtaCor));
			if (mvtofinVO.idFornecedor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", mvtofinVO.idFornecedor));
			if (mvtofinVO.idCliente == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", mvtofinVO.idCliente));
			if (mvtofinVO.idFormaPgto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", mvtofinVO.idFormaPgto));
			if (mvtofinVO.idPlnCta == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", mvtofinVO.idPlnCta));
			if (mvtofinVO.idCLucro == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCLucro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCLucro", mvtofinVO.idCLucro));
			if (mvtofinVO.idCCusto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", mvtofinVO.idCCusto));
			if (mvtofinVO.idCaixa == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixa", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixa", mvtofinVO.idCaixa));
			if (mvtofinVO.idTitRec == null)
				cmd.Parameters.Add(getParameter(cmd, "@idTitRec", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idTitRec", mvtofinVO.idTitRec));
			if (mvtofinVO.idTitPag == null)
				cmd.Parameters.Add(getParameter(cmd, "@idTitPag", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idTitPag", mvtofinVO.idTitPag));
            if (mvtofinVO.nrDoc == null)
                cmd.Parameters.Add(getParameter(cmd, "@nrDoc", System.DBNull.Value));
            else
                cmd.Parameters.Add(getParameter(cmd, "@nrDoc", mvtofinVO.nrDoc));
			cmd.Parameters.Add(getParameter(cmd, "@dtMvtoFin", mvtofinVO.dtMvtoFin));
			cmd.Parameters.Add(getParameter(cmd, "@valor", mvtofinVO.valor));
			if (mvtofinVO.valorJuro == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorJuro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorJuro", mvtofinVO.valorJuro));
			if (mvtofinVO.valorDesc == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorDesc", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorDesc", mvtofinVO.valorDesc));
			if (mvtofinVO.valorPago == null)
				cmd.Parameters.Add(getParameter(cmd, "@valorPago", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@valorPago", mvtofinVO.valorPago));
			cmd.Parameters.Add(getParameter(cmd, "@natureza", mvtofinVO.natureza));
            if (mvtofinVO.conciliacao == null || String.IsNullOrEmpty(mvtofinVO.conciliacao.ToString()))
                cmd.Parameters.Add(getParameter(cmd, "@conciliacao", 'N'));
            else
                cmd.Parameters.Add(getParameter(cmd, "@conciliacao", mvtofinVO.conciliacao));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", mvtofinVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", mvtofinVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", mvtofinVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", mvtofinVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('mvtofin') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delMvtofin(MvtofinVO mvtofinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM mvtofin ");
			query.AppendLine("WHERE idMvtoFin=@idMvtoFin ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", mvtofinVO.idMvtoFin));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountMvtofinxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM mvtofin WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdCtaCor(int? idCtaCor)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCtaCor) as ocorrencias FROM mvtofin WHERE IdCtaCor=@IdCtaCor ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCtaCor", idCtaCor));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdFornecedor(int? idFornecedor)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFornecedor) as ocorrencias FROM mvtofin WHERE IdFornecedor=@IdFornecedor ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFornecedor", idFornecedor));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdCliente(int? idCliente)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCliente) as ocorrencias FROM mvtofin WHERE IdCliente=@IdCliente ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCliente", idCliente));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdFormaPgto(int? idFormaPgto)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFormaPgto) as ocorrencias FROM mvtofin WHERE IdFormaPgto=@IdFormaPgto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFormaPgto", idFormaPgto));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdPlnCta(int? idPlnCta)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPlnCta) as ocorrencias FROM mvtofin WHERE IdPlnCta=@IdPlnCta ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPlnCta", idPlnCta));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdCLucro(int? idCLucro)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCLucro) as ocorrencias FROM mvtofin WHERE IdCLucro=@IdCLucro ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCLucro", idCLucro));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdCCusto(int? idCCusto)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCCusto) as ocorrencias FROM mvtofin WHERE IdCCusto=@IdCCusto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCCusto", idCCusto));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdCaixa(int? idCaixa)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCaixa) as ocorrencias FROM mvtofin WHERE IdCaixa=@IdCaixa ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCaixa", idCaixa));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdTitRec(int? idTitRec)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idTitRec) as ocorrencias FROM mvtofin WHERE IdTitRec=@IdTitRec ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdTitRec", idTitRec));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountMvtofinxIdTitPag(int? idTitPag)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idTitPag) as ocorrencias FROM mvtofin WHERE IdTitPag=@IdTitPag ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdTitPag", idTitPag));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
